package DianShang_2024.ds_server.indicator

import org.apache.spark.sql.SparkSession

object trait04 {
  def main(args: Array[String]): Unit = {
    /*
        4、请根据dwd或者dws层表计算出每个城市每个月平均订单金额和该城市所在省份订单金额中位数相比较结果（“高/低/相同”）,存入ClickHouse数据库shtd_result
        的citymidcmpprovince表中（表结构如下），然后在Linux的ClickHouse命令行中根据城市平均订单金额、省份平均订单金额均为降序排序，查询出前5条；
     */
    //  准备sparksql的环境
    val spark=SparkSession.builder()
      .master("local[*]")
      .appName("指标计算第四题")
      .enableHiveSupport()
      .getOrCreate()

spark.sql("use dwd_server")


      /*
            percentile_approx(order_money,0.5):求order_money字段的中位数
       */
      //  city_table:首先拿到每个城市每个月的订单金额中位数
    spark.sql(
      """
        |select
        |city as city_name,
        |province as province_name,
        |month(date_format(to_timestamp(create_time,'yyyyMMdd'),'yyyy-MM-dd')) as month,
        |percentile_approx(order_money,0.5) as CityMonthMedian
        |from dwd_server.fact_order_master
        |group by province_name,city_name,month
        |""".stripMargin).createOrReplaceTempView("city_table")

    spark.sql("select * from city_table limit 20").show

    //  province_table:拿到每个省份每个月的订单金额中位数(median:中位数)
    spark.sql(
      """
        |select
        |province as province_name,
        |month(date_format(to_timestamp(create_time,'yyyyMMdd'),'yyyy-MM-dd')) as month,
        |percentile_approx(order_money,0.5) as ProvinceMonthMedian
        |from dwd_server.fact_order_master
        |group by province_name,month
        |""".stripMargin).createOrReplaceTempView("province_table")

    spark.sql("select * from province_table limit 20 ").show



    //  将省份和城市的中位数表合并，并且判断得到comparison: 比较
    val result_data=spark.sql(
      """
        |select
        |t1.province_name as province_name,
        |t1.city_name as city_name,
        |t1.month as month,
        |t1.CityMonthMedian  as CityMonthMedian,
        |t2.ProvinceMonthMedian as ProvinceMonthMedian,
        |if(
        |t1.CityMonthMedian > t2.ProvinceMonthMedian, '高',if(
        |t1.CityMonthMedian < t2.ProvinceMonthMedian ,'低','相同'
        |)
        |) as comparison
        |from city_table as t1
        |join province_table as t2
        |on t1.province_name=t2.province_name
        |group by t1.province_name,t1.city_name,t1.month,CityMonthMedian,ProvinceMonthMedian
        |""".stripMargin)

//    result_data.createOrReplaceTempView("result_table")
//    spark.sql(
//      """
//        |select
//        |province_name,
//        |city_name,
//        |month
//        |from result_table
//        |where month = null
//        |""".stripMargin).show

    //  检查数据
//    spark.sql("select * from result_table  limit 20").show

    //  将数据加载到clickhouse
    result_data.write
      .format("jdbc")
      .option("url","jdbc:clickhouse://192.168.40.110:8123/shtd_result")
      .option("user","default")
      .option("password","")
      .option("driver","com.clickhouse.jdbc.ClickHouseDriver")
      .option("dbtable","citymidcmpprovince")
      .mode("append")
      .save()


    //  关闭sparksql的环境
    spark.close()
  }

}
